USE [IC.NET_NEW]
GO
/*
    Add original foreign key columns
*/
ALTER TABLE EmergencyMessageContainer
ADD DEKey uniqueidentifier null
GO
ALTER TABLE DistributionReference
ADD DEKey uniqueidentifier null
GO
ALTER TABLE RecipientRole
ADD DEKey uniqueidentifier null
GO
ALTER TABLE TargetArea
ADD DEKey uniqueidentifier null
GO
ALTER TABLE Recipient
ADD DEKey uniqueidentifier null
GO
ALTER TABLE SenderRole
ADD DEKey uniqueidentifier null
GO
ALTER TABLE ContainerTopic
ADD DEKey uniqueidentifier null
GO
ALTER TABLE EmergencyMessageContent
ADD DEKey uniqueidentifier null
GO
ALTER TABLE EmergencyMessageContent
ADD ContentObjectKey uniqueidentifier null
GO
ALTER TABLE NonXMLContent
ADD ContentObjectKey uniqueidentifier null
GO
ALTER TABLE Other
ADD ContentObjectKey uniqueidentifier null
GO
ALTER TABLE XMLContent
ADD ContentObjectKey uniqueidentifier null
GO
ALTER TABLE ContentTopic
ADD ContentObjectKey uniqueidentifier null
GO
ALTER TABLE OriginatorRole
ADD ContentObjectKey uniqueidentifier null
GO
ALTER TABLE ConsumerRole
ADD ContentObjectKey uniqueidentifier null
GO
ALTER TABLE ExposureCache
ADD ContentObjectKey uniqueidentifier null
GO
ALTER TABLE ExposureCache
ADD ExposureKey uniqueidentifier null
GO
ALTER TABLE ExposureCategory
ADD ExposureKey uniqueidentifier null
GO
ALTER TABLE ExposureCacheRole
ADD ExposureKey uniqueidentifier null
GO
/*
    Disable foreign key constraints
*/
ALTER TABLE DistributionReference NOCHECK CONSTRAINT FK_DistributionReference_EmergencyMessageContainer;
ALTER TABLE RecipientRole NOCHECK CONSTRAINT FK_RecipientRole_EmergencyMessageContainer;
ALTER TABLE TargetArea NOCHECK CONSTRAINT FK_TargetArea_EmergencyMessageContainer;
ALTER TABLE Recipient NOCHECK CONSTRAINT FK_Recipient_EmergencyMessageContainer;
ALTER TABLE SenderRole NOCHECK CONSTRAINT FK_SenderRole_EmergencyMessageContainer;
ALTER TABLE ContainerTopic NOCHECK CONSTRAINT FK_ContainerTopic_EmergencyMessageContainer;
ALTER TABLE EmergencyMessageContent NOCHECK CONSTRAINT FK_EmergencyMessageContent_EmergencyMessageContainer;
ALTER TABLE NonXMLContent NOCHECK CONSTRAINT FK_NonXMLContent_EmergencyMessageContent;
ALTER TABLE Other NOCHECK CONSTRAINT FK_Other_EmergencyMessageContent;
ALTER TABLE XMLContent NOCHECK CONSTRAINT FK_XMLContent_EmergencyMessageContent;
ALTER TABLE ContentTopic NOCHECK CONSTRAINT FK_ContentTopic_EmergencyMessageContent;
ALTER TABLE OriginatorRole NOCHECK CONSTRAINT FK_OriginatorRole_EmergencyMessageContent;
ALTER TABLE ConsumerRole NOCHECK CONSTRAINT FK_ConsumerRole_EmergencyMessageContent;
ALTER TABLE ExposureCache NOCHECK CONSTRAINT FK_ExposureCache_EmergencyMessageContent;
ALTER TABLE ExposureCategory NOCHECK CONSTRAINT FK_ExposureCategory_ExposureCache;
ALTER TABLE ExposureCacheRole NOCHECK CONSTRAINT FK_ExposureCacheRole_ExposureCache;
/*
    Insert into tables
*/
INSERT INTO EmergencyMessageContainer (
DEKey
,DistributionCode
,SenderCode
,DateTimeSent
,DistributionStatus
,DistributionType
,CombinedConfidentiality
,Language
,DEXML)
SELECT
DEKey
,DistributionID
,SenderID
,DateTimeSent
,DistributionStatus
,DistributionType
,CombinedConfidentiality
,Language
,DEXML
FROM [IC.NET]..DEMain
GO
INSERT INTO DistributionReference (
DEKey
,DistributionReference
,EmergencyMessageContainerId)
SELECT
DEKey
,DistributionReference
,0
FROM [IC.NET]..DEDistributionReference
GO
INSERT INTO RecipientRole (
DEKey
,ListURI
,Value
,EmergencyMessageContainerId)
SELECT
DEKey
,ListURI
,RecipientRole
,0
FROM [IC.NET]..DERecipientRole
GO
INSERT INTO SenderRole (
DEKey
,ListURI
,Value
,EmergencyMessageContainerId
)
SELECT
DEKey
,ListURI
,SenderRole
,0
FROM [IC.NET]..DESenderRole
GO
DISABLE TRIGGER TargetAreaInsert ON TargetArea
GO
INSERT INTO TargetArea (
DEKey
,Polygon
,Country
,SubDivision
,UNLocCode
,Circle
,EmergencyMessageContainerId
)
SELECT
DEKey
,Polygon
,Country
,SubDivision
,UNLocCode
,Circle
,0
FROM [IC.NET]..DETargetArea
GO
ENABLE TRIGGER TargetAreaInsert ON TargetArea
GO
INSERT INTO Recipient (
DEKey
,ExplicitAddressScheme
,Value
,EmergencyMessageContainerId
)
SELECT
DEKey
,ExplicitAddressScheme
,ExplicitAddress
,0
FROM [IC.NET]..DEExplicitAddress
GO
INSERT INTO ContainerTopic (
DEKey
,ListURI
,Value
,EmergencyMessageContainerId
)
SELECT
DEKey
,ListURI
,Keyword
,0
FROM [IC.NET]..DEKeyword
GO
INSERT INTO EmergencyMessageContent (
DEKey
,ContentObjectKey
,ContentDescription
,IncidentCode
,IncidentDescription
,Confidentiality
,Type
,EmergencyMessageContainerId
,DSCHash
)
SELECT
DEKey
,ContentObjectKey
,ContentDescription
,IncidentId
,IncidentDescription
,Confidentiality
,Type
,0
,0
FROM [IC.NET]..DEContentObject
GO
INSERT INTO NonXMLContent (
ContentObjectKey
,MIMEType
,Size
,Digest
,Uri
,BinaryContentData
,BinaryContentID
,EmergencyMessageContentId
)
SELECT
ContentObjectKey
,MIMEType
,Size
,Digest
,Uri
,BinaryContentData
,BinaryContentID
,0
FROM [IC.NET]..DENonXMLContent
GO
INSERT INTO Other (
ContentObjectKey
,Signature
,EmergencyMessageContentId
)
SELECT
ContentObjectKey
,Other
,0
FROM [IC.NET]..DEOther
GO
INSERT INTO XMLContent (
ContentObjectKey
,KeyXMLContent
,EmbeddedXMLContent
,EmergencyMessageContentId
)
SELECT
ContentObjectKey
,KeyXMLContent
,EmbeddedXMLContent
,0
FROM [IC.NET]..DEXMLContent
GO
INSERT INTO ContentTopic (
ContentObjectKey
,ListURI
,Value
,EmergencyMessageContentId
)
SELECT
ContentObjectKey
,ListURI
,ContentKeyword
,0
FROM [IC.NET]..DEContentKeyword
GO
INSERT INTO OriginatorRole (
ContentObjectKey
,ListURI
,Value
,EmergencyMessageContentId
)
SELECT
ContentObjectKey
,ListURI
,OriginatorRole
,0
FROM [IC.NET]..DEOriginatorRole
GO
INSERT INTO ConsumerRole(
ContentObjectKey
,ListURI
,Value
,EmergencyMessageContentId
)
SELECT
ContentObjectKey
,ListURI
,ConsumerRole
,0
FROM [IC.NET]..DEConsumerRole
GO
DISABLE TRIGGER ExposureCacheInsert ON ExposureCache
GO
INSERT INTO ExposureCache(
ContentObjectKey
,ExposureKey
,Author
,BaseUri
,Content
,Copyright
,LastUpdatedTime
,Link
,PublishDate
,Summary
,Title
,GeoRSSWhere
,Circle
,BoxUpper
,BoxLower
,EmergencyMessageContentId
)
SELECT
ContentKey
,ExposureKey
,Author
,BaseUri
,Content
,Copyright
,LastUpdatedTime
,Link
,PublishDate
,Summary
,Title
,GeoRSSWhere
,Circle
,BoxUpper
,BoxLower
,0
FROM [IC.NET]..ExposureCache
GO
ENABLE TRIGGER ExposureCacheInsert ON ExposureCache
GO
INSERT INTO ExposureCategory(
ExposureKey
,Label
,Name
,Scheme
,ExposureCacheId
)
SELECT
ExposureKey
,Label
,Name
,Scheme
,0
FROM [IC.NET]..ExposureCategory
GO
INSERT INTO ExposureCacheRole(
ExposureKey
,ListURI
,Value
,ExposureCacheId
)
SELECT
ExposureKey
,ListURI
,Role
,0
FROM [IC.NET]..ExposureCacheRole
GO
/*
    Update foreign key columns
*/
UPDATE DistributionReference
SET EmergencyMessageContainerId = (
SELECT Id FROM EmergencyMessageContainer
WHERE EmergencyMessageContainer.DEKey = DistributionReference.DEKey)
GO
UPDATE RecipientRole
SET EmergencyMessageContainerId = (
SELECT Id FROM EmergencyMessageContainer
WHERE EmergencyMessageContainer.DEKey = RecipientRole.DEKey)
GO
UPDATE TargetArea
SET EmergencyMessageContainerId = (
SELECT Id FROM EmergencyMessageContainer
WHERE EmergencyMessageContainer.DEKey = TargetArea.DEKey)
GO
UPDATE Recipient
SET EmergencyMessageContainerId = (
SELECT Id FROM EmergencyMessageContainer
WHERE EmergencyMessageContainer.DEKey = Recipient.DEKey)
GO
UPDATE SenderRole
SET EmergencyMessageContainerId = (
SELECT Id FROM EmergencyMessageContainer
WHERE EmergencyMessageContainer.DEKey = SenderRole.DEKey)
GO
UPDATE ContainerTopic
SET EmergencyMessageContainerId = (
SELECT Id FROM EmergencyMessageContainer
WHERE EmergencyMessageContainer.DEKey = ContainerTopic.DEKey)
GO
UPDATE EmergencyMessageContent
SET EmergencyMessageContainerId = (
SELECT Id FROM EmergencyMessageContainer
WHERE EmergencyMessageContainer.DEKey = EmergencyMessageContent.DEKey)
GO
UPDATE NonXMLContent
SET EmergencyMessageContentId = (
SELECT Id FROM EmergencyMessageContent
WHERE EmergencyMessageContent.ContentObjectKey = NonXMLContent.ContentObjectKey)
GO
UPDATE Other
SET EmergencyMessageContentId = (
SELECT Id FROM EmergencyMessageContent
WHERE EmergencyMessageContent.ContentObjectKey = Other.ContentObjectKey)
GO
UPDATE XMLContent
SET EmergencyMessageContentId = (
SELECT Id FROM EmergencyMessageContent
WHERE EmergencyMessageContent.ContentObjectKey = XMLContent.ContentObjectKey)
GO
UPDATE ContentTopic
SET EmergencyMessageContentId = (
SELECT Id FROM EmergencyMessageContent
WHERE EmergencyMessageContent.ContentObjectKey = ContentTopic.ContentObjectKey)
GO
UPDATE OriginatorRole
SET EmergencyMessageContentId = (
SELECT Id FROM EmergencyMessageContent
WHERE EmergencyMessageContent.ContentObjectKey = OriginatorRole.ContentObjectKey)
GO
UPDATE ConsumerRole
SET EmergencyMessageContentId = (
SELECT Id FROM EmergencyMessageContent
WHERE EmergencyMessageContent.ContentObjectKey = ConsumerRole.ContentObjectKey)
GO
UPDATE ExposureCache
SET EmergencyMessageContentId = (
SELECT Id FROM EmergencyMessageContent
WHERE EmergencyMessageContent.ContentObjectKey = ExposureCache.ContentObjectKey)
GO
UPDATE ExposureCategory
SET ExposureCacheId = (
SELECT Id FROM ExposureCache
WHERE ExposureCache.ExposureKey = ExposureCategory.ExposureKey)
GO
UPDATE ExposureCacheRole
SET ExposureCacheId = (
SELECT Id FROM ExposureCache
WHERE ExposureCache.ExposureKey = ExposureCacheRole.ExposureKey)
GO
/*
    Enable foreign key constraints
*/
ALTER TABLE DistributionReference CHECK CONSTRAINT FK_DistributionReference_EmergencyMessageContainer;
ALTER TABLE RecipientRole CHECK CONSTRAINT FK_RecipientRole_EmergencyMessageContainer;
ALTER TABLE TargetArea CHECK CONSTRAINT FK_TargetArea_EmergencyMessageContainer;
ALTER TABLE Recipient CHECK CONSTRAINT FK_Recipient_EmergencyMessageContainer;
ALTER TABLE SenderRole CHECK CONSTRAINT FK_SenderRole_EmergencyMessageContainer;
ALTER TABLE ContainerTopic CHECK CONSTRAINT FK_ContainerTopic_EmergencyMessageContainer;
ALTER TABLE EmergencyMessageContent CHECK CONSTRAINT FK_EmergencyMessageContent_EmergencyMessageContainer;
ALTER TABLE NonXMLContent CHECK CONSTRAINT FK_NonXMLContent_EmergencyMessageContent;
ALTER TABLE Other CHECK CONSTRAINT FK_Other_EmergencyMessageContent;
ALTER TABLE XMLContent CHECK CONSTRAINT FK_XMLContent_EmergencyMessageContent;
ALTER TABLE ContentTopic CHECK CONSTRAINT FK_ContentTopic_EmergencyMessageContent;
ALTER TABLE OriginatorRole CHECK CONSTRAINT FK_OriginatorRole_EmergencyMessageContent;
ALTER TABLE ConsumerRole CHECK CONSTRAINT FK_ConsumerRole_EmergencyMessageContent;
ALTER TABLE ExposureCache CHECK CONSTRAINT FK_ExposureCache_EmergencyMessageContent;
ALTER TABLE ExposureCategory CHECK CONSTRAINT FK_ExposureCategory_ExposureCache;
ALTER TABLE ExposureCacheRole CHECK CONSTRAINT FK_ExposureCacheRole_ExposureCache;
/*
    Drop original foreign key columns
*/
ALTER TABLE EmergencyMessageContainer DROP COLUMN DEKey;
ALTER TABLE DistributionReference DROP COLUMN DEKey;
ALTER TABLE RecipientRole DROP COLUMN DEKey;
ALTER TABLE TargetArea DROP COLUMN DEKey;
ALTER TABLE Recipient DROP COLUMN DEKey;
ALTER TABLE SenderRole DROP COLUMN DEKey;
ALTER TABLE ContainerTopic DROP COLUMN DEKey;
ALTER TABLE EmergencyMessageContent DROP COLUMN DEKey;
ALTER TABLE EmergencyMessageContent DROP COLUMN ContentObjectKey;
ALTER TABLE NonXMLContent DROP COLUMN ContentObjectKey;
ALTER TABLE Other DROP COLUMN ContentObjectKey;
ALTER TABLE XMLContent DROP COLUMN ContentObjectKey;
ALTER TABLE ContentTopic DROP COLUMN ContentObjectKey;
ALTER TABLE OriginatorRole DROP COLUMN ContentObjectKey;
ALTER TABLE ConsumerRole DROP COLUMN ContentObjectKey;
ALTER TABLE ExposureCache DROP COLUMN ExposureKey;
ALTER TABLE ExposureCache DROP COLUMN ContentObjectKey;
ALTER TABLE ExposureCategory DROP COLUMN ExposureKey;
ALTER TABLE ExposureCacheRole DROP COLUMN ExposureKey;
